package core.rpt;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellReference;

import com.mysql.jdbc.StringUtils;


public class ExcelUtil {
	private static final Map<String, Integer> alphNumberMap = new HashMap<String, Integer>();

    static {
        alphNumberMap.put("A", 0);
        alphNumberMap.put("B", 1);
        alphNumberMap.put("C", 2);
        alphNumberMap.put("D", 3);
        alphNumberMap.put("E", 4);
        alphNumberMap.put("F", 5);
        alphNumberMap.put("G", 6);
        alphNumberMap.put("H", 7);
        alphNumberMap.put("I", 8);
        alphNumberMap.put("J", 9);
        alphNumberMap.put("K", 10);
        alphNumberMap.put("L", 11);
        alphNumberMap.put("M", 12);
        alphNumberMap.put("N", 13);
        alphNumberMap.put("O", 14);
        alphNumberMap.put("P", 15);
        alphNumberMap.put("Q", 16);
        alphNumberMap.put("R", 17);
        alphNumberMap.put("S", 18);
        alphNumberMap.put("T", 19);
        alphNumberMap.put("U", 20);
        alphNumberMap.put("V", 21);
        alphNumberMap.put("W", 22);
        alphNumberMap.put("X", 23);
        alphNumberMap.put("Y", 24);
        alphNumberMap.put("Z", 25);
    }

    public static int getColumnIndexByAlphName(final String alphName) {
        Double index = 0.0;
        char[] chars = alphName.toCharArray();
        for (int i = chars.length - 1; i > -1; i--) {
            Integer idx = alphNumberMap.get(String.valueOf(chars[i]));
            if (i == chars.length - 1) {
                index += idx;
            } else {
                index += Math.pow(26, chars.length - i - 1) * (idx + 1);
            }
        }
        return index.intValue();
    }
    
    public static String getColumnAlphNameByIndex(final int idx) {
    	return CellReference.convertNumToColString(idx);
    }

	public static Row getRow(Sheet sheet, int rowIdx) {
		Row row = sheet.getRow(rowIdx);
		if (row == null) {
			row = sheet.createRow(rowIdx);
		}
		return row;
	}
	
	public static Cell getCell(Row row, int cellIdx) {
		Cell cell = row.getCell(cellIdx);
		if (cell == null) {
			cell = row.createCell(cellIdx);
		}
		cell.getCellStyle().setAlignment(CellStyle.ALIGN_CENTER);
		return cell;
	}
	
	public static void setCellValue(Row row, int cellIdx, String value) {
		Cell cell = getCell(row, cellIdx);
		cell.setCellValue(value);
	}
	
	
	public static void setCellValue(Row row, int cellIdx, BigDecimal value, int scale) {
		if (value == null) {
			value = new BigDecimal(0);
		}
		Cell cell = getCell(row, cellIdx);
		cell.setCellValue(value.setScale(scale, RoundingMode.HALF_UP).doubleValue());
	}
	
	public static void setCellValue(Row row, int cellIdx, Long value) {
		Cell cell = getCell(row, cellIdx);
		cell.setCellValue(value);
	}

    public static String getCellValue(final Row row, final int col, final boolean isDate) {
        Cell cell = row.getCell(col);
        if (cell == null) {
            return "";
        }

        int cellType = cell.getCellType();
        String value = null;
        try {
            switch (cellType) {
                case Cell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                case Cell.CELL_TYPE_BOOLEAN:
                    value = cell.getStringCellValue();
                case Cell.CELL_TYPE_NUMERIC:
                    if (isDate) {
                        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                        value = format.format(cell.getDateCellValue());
                    } else {
                        value = String.valueOf(new Double(cell.getNumericCellValue()).longValue());
                    }
                default:
                    break;
            }
        } catch (Exception e) {
            value = cell.getStringCellValue();
        }

        return StringUtils.isNullOrEmpty(value) ? "" : value.trim().replace("'", "''");
    }
}
